Upgrade Postgres
PGDay Paris
2023-03-23
Image by
Gerd Altmann
from
Pixabay
Who am I
Lætitia Avrot
PostgreSQL recognized contributor
PostgreSQL Europe board member
#PostgresWomen
co-founder
EDB
Field CTO
@l_avrot@fosstodon.org
mydbanotebook.org
/
psql-tips.org
Image by
Anemone123
from
Pixabay
Upgrading Postgres
Postgres roadmap
Why upgrade?
What to do when
Image by
Jan Vašek
from
Pixabay
Postgres Roadmap
Patches creation
Commit fests
Minor/Major
Feature freeze
Image by
Valentin
from
Pixabay
Postgres Roadmap
Commit fests
1 month
5 times a year
Everyone is welcome!
Image by
ktphotography
from
Pixabay
Minor versions
No new feature
bug fix
security fix
At least once per quarter
Read the release notes!
Image by
Daniel Albany
from
Pixabay
Major versions
New features
Once a year
During fall
Feature freeze
Very strict
Everything after -> next version
Why upgrade?
To be secured
To keep up
To give back to the community
Image by
Pexels
from
Pixabay
Security
That's important!
No code is bug-free
No code is secured
Flaws are published after minor releases
Image by bearfotos
on Freepik
Softwares validated against a minor version
non-sense
creates security flaws in IT architecture
shows how little you know about Postgres
Image by Racool_studio
on Freepik
Postgres supported versions policy
5 major versions
Favoring retrocompliance
But we still need to keep going
Image by fxquadro
on Freepik
Keeping up
Unsupported versions are a risk
Avoiding big jumps of versions
Benefiting of performance enhancements
Benefiting of new features
Image by
Shri ram
from
Pixabay
Giving back to the community
Test the beta in recipe
Send bugs to
pgsql-bugs@lists.postgresql.org
Image by
NoName_13
from
Pixabay
How to upgrade?
Tools
What is the best solution?
It depends!
Image by wayhomestudio
on Freepik
Can you afford a service disruption?
Yes.
How much?
No.
Are you sure? even 5 minutes?
Image by
Steve Buissinne
from
Pixabay
Release notes
Read them!
All of them!
That's right, all between your minor version to your minor target version!
Image by
Jess Bailey
from
Pixabay
Tools
Exporting/importing
pg_upgrade
Logical Replication
Image by
4956365
from
Pixabay
Exporting/importing
Service disruption (duration depends on the volume of data)
Removes bloat
Removes index corruption(if any)
Image by
postcardtrip
from
Pixabay
Exporting/importing
Install the new version of Postgres
Create the new instance
Take a physical backup
Test your backup
Image by
Steve Buissinne
from
Pixabay
Exporting/importing
Stop the app
Make sure you have all the WALs
Export the non database objects
Export your databases one by one
Import the non database objects
Image by
Steve Buissinne
from
Pixabay
Exporting/importing
Import the databases one by one
Stop the old instance
Configure and start the app
Take another backup
Image by
Steve Buissinne
from
Pixabay
pg_upgrade
Service disruption
Link mode
Simple
Image by
Joe
from
Pixabay
pg_upgrade
Install the new version of Postgres
Create the new instance
Take a physical backup
Test your backup
Image by
Joe
from
Pixabay
pg_upgrade
Use pg_upgrade
Start the app
Take another backup
Image by
Joe
from
Pixabay
Logical Replication
Near-zero service disruption
More complex (Sequences)
Image by
1035352
from
Pixabay
Logical Replication
Install the new version of Postgres
Create the new instance
Setup the logical replication
Stop the app
Image by
1035352
from
Pixabay
Logical Replication
Set the sequences
Configure and start the app
Remove the logical replication
Take another backup
Image by
1035352
from
Pixabay
One big problem: extensions
Compliance with Postgres versions
Migration path
Add other tool compliance into the mix
One solution: avoid big jumps and plan ahead!
Image by
Tim Hill
from
Pixabay
Questions?
Image by
Jon Kline
from
Pixabay